﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class dal_SearchByParameters
    {
        /// <summary>
        /// 根据参数条件查询
        /// </summary>
        /// <param name="model">开奖信息对象</param>
        /// <returns>根据参数条件查询到DataTable类型的历史开奖信息</returns>
        public DataTable SearchByParameters(ResultClass model,List<DateTime> searchDate)
        {
            StringBuilder sbSQL = new StringBuilder(@"select 
	                                                     cbr.id,cbr.Name,cbr.Code,cbr.DetailsLink,cbr.VideoLink,cbr.AwardDate,cbr.AwardWeek,
	                                                     cbr.Red,cbr.Red1,cbr.Red2,cbr.Red3,cbr.Red4,cbr.Red5,cbr.Red6,cbr.blue,cbr.Sales,cbr.PoolMoney,
	                                                     cbr.ContentArea,cbr.addmoney,cbr.addmoney2,cbr.msg,cbr.z2add,cbr.m2add,pg.id as PrizegradeID,
	                                                     pg.typelevel as PrizeGradeLevel,pg.typenum as PrizeGradeNum,pg.typeMoney as PrizeGradeMoney
	                                                     from ColorBallResult as cbr inner join PrizeGrades as pg on cbr.PrizeGradesID=pg.id ");
            //StringBuilder sbSQL = new StringBuilder();
            List<string> wheres = new List<string>();
            List<SqlParameter> listParams = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(model.Code))
            {
                wheres.Add(" cbr.code like @code ");
                listParams.Add(new SqlParameter("@code", SqlDbType.NVarChar, 50) { Value = "%" + model.Code + "%" });
            }
            if (!string.IsNullOrEmpty(model.AwardDate))
            {
                wheres.Add(" cbr.AwardDate like @AwardDate ");
                listParams.Add(new SqlParameter("@AwardDate", SqlDbType.Date) { Value = model.AwardDate });
            }
            if (!string.IsNullOrEmpty(model.Red))
            {
                wheres.Add(" cbr.Red like @Red ");
                listParams.Add(new SqlParameter("@Red", SqlDbType.NVarChar, 50) { Value = "%" + model.Red + "%" });
            }
            if (searchDate.Count!=0)
            {
                //wheres.Add(" cbr.AwardDate BETWEEN '@BeginDate' AND '@EndDate' ");
                wheres.Add(" Convert(date,cbr.AwardDate) BETWEEN Convert(date,@BeginDate) AND Convert(date,@EndDate) ");
                listParams.Add(new SqlParameter("@BeginDate", SqlDbType.NVarChar,50) { Value = searchDate[0].ToShortDateString()});
                listParams.Add(new SqlParameter("@EndDate", SqlDbType.NVarChar, 50) { Value = searchDate[1].ToShortDateString()}) ;
            }

            if (wheres.Count > 0)
            {
                sbSQL.Append(" where ");
                sbSQL.Append(string.Join(" and ", wheres));
                listParams.Add(new SqlParameter("@sqlStr", SqlDbType.NVarChar, 1000) { Value = sbSQL.ToString() });
                SqlParameter[] pms = listParams.ToArray();
                /*string sql = @"select si.autoId,si.stuID,si.stuName,si.stuIDNum,si.stuPhone,si.stuGender,si.stuAddress,si.stuFirstContactDate,
                                        si.stuSigeUpDate,si.stuChargesType,si.stuCharges,si.stuActualfees,si.QualificationCertificate,si.SigeUpType,
                                        si.Introducer,si.KM2TrainingDate,si.KM3TrainingDate,si.Graduation,si.IsNormal,si.IsDelete,	si.IsRefund,si.Remarks,
                                        ct.CarTypeId as stuCarTypeId,ct.CarTypeName as stuCarTypeName,	stuArrears = (select a.Expenses - b.ActualExpenses - c.Discount from--计算欠费开始
                                        (select sum(Expenses) as Expenses from ChargeRecords where stuID = si.stuID)a,
                                        (select sum(ActualExpenses) as ActualExpenses from ChargeRecords where stuID = si.stuID)b,
                                        (select sum(Discount) as Discount from ChargeRecords where stuID = si.stuID)c),--计算欠费结束
                                        ct.CarTypeId as stuCarTypeId,ct.CarTypeName as stuCarTypeName
                                    from  StuInfo as si inner join CarType as ct on si.stuCarType = ct.CarTypeId ";*/
                //return SqlHelper.SqlDataTable("exec usp_SearchStuInfoByWhere @sqlStr", CommandType.StoredProcedure, pms);
                return SqlHelper.SqlDataTable(sbSQL.ToString(), CommandType.Text, pms);
            }
            else
            {
                return null;
            }
        }
    }
}

